﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class PersonaDB
    Private Shared cmd2 As SqlCommand
    Public Shared Function GetDireccionesPersona(ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer) As DataSet
        Dim cmd As New SqlCommand("spGetDireccionesPersona", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)

        da.SelectCommand = cmd
        da.Fill(ds, "DireccionesPersona")
        Return ds
    End Function

    Public Shared Function GetEmpleadosyPersonas() As DataSet
        Dim cmd As New SqlCommand("spGetEmpleadosyPersonas", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure
      
        da.SelectCommand = cmd
        da.Fill(ds, "EmpleadosyPersonas")
        Return ds
    End Function

    Public Shared Function GetEmpleadosyPersonasByApellido(ByVal apellido As String) As DataSet
        Dim cmd As New SqlCommand("spGetEmpleadosyPersonas", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        If apellido = "" Then
            cmd.Parameters.AddWithValue("@apellido", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@apellido", apellido)
        End If

        cmd.CommandType = CommandType.StoredProcedure

        da.SelectCommand = cmd
        da.Fill(ds, "EmpleadosyPersonas")
        Return ds
    End Function
    Public Shared Sub InsertarPersona(ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer, ByVal nombre As String, ByVal apellido As String, ByVal fecha_nacimiento As Date, ByVal email As String)
        Dim cmd As New SqlCommand("spInsertarPersona", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@nombre", nombre)

        cmd.Parameters.AddWithValue("@apellido", apellido)
        cmd.Parameters.AddWithValue("@fecha_nacimiento", fecha_nacimiento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)

        If email = "" Then
            cmd.Parameters.AddWithValue("@email", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@email", email)
        End If

        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

    End Sub
    Public Shared Function GetPersonaByDocumento(ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer) As SqlDataReader
        cmd2 = New SqlCommand("spGetPersonas", DB.Conexion)

        Dim dr As SqlDataReader

        cmd2.CommandType = CommandType.StoredProcedure
        cmd2.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd2.Parameters.AddWithValue("@nro_documento", nro_documento)

        cmd2.Connection.Open()

        dr = cmd2.ExecuteReader

        Return dr
    End Function

    Public Shared Sub CerrarConexion()
        cmd2.Connection.Close()
    End Sub

    Public Shared Sub ActualizarPersona(ByVal nombre As String, ByVal apellido As String, ByVal fecha_nacimiento As Date, ByVal nro_documento As Integer, ByVal id_tipo_documento As Integer, ByVal email As String)
        Dim cmd As New SqlCommand("spUpdatePersona", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@nombre", nombre)
        cmd.Parameters.AddWithValue("@apellido", apellido)
        cmd.Parameters.AddWithValue("@fecha_nacimiento", fecha_nacimiento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        If email = "" Then
            cmd.Parameters.AddWithValue("@email", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@email", email)
        End If

        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

    End Sub

    Public Shared Sub EliminarPersona(ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer)
        Dim cmd As New SqlCommand("spDeletePersona", DB.Conexion)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        cmd.Parameters.Add("@error", SqlDbType.Int)
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If
    End Sub
End Class
